﻿#encoding 'utf-8'

=begin
    用于tmall程序的各种bug修复及数据修复
=end

load 'rds_api.rb'
load 'tutem_utils.rb'

#通过运行findMissingProductAttrs来完成barcode弥补
def showMissingProductAttrs

    attrs = []
    sql = "select id, product_attr_unique as attr from tutem_mall.eb_store_cart group by attr"
    (queryRds(sql)).each do |q|
        attrs += [q['attr']]
    end

    existed = barcodeList.keys

    missed = {}
    leaked = attrs - existed
    leaked.each do |k|
        sql = "select cart_info from tutem_mall.eb_store_order_cart_info where cart_info like '%#{k}%' limit 1"
        (queryRds(sql)).each do |q|
            cart = JSON.parse q['cart_info']
            attr = cart['productInfo']['attrInfo']
            info = "unique: #{k} bcode:#{attr['bar_code']}, suk: #{attr['suk']}, unique: #{attr['unique']}, product_name: #{cart['productInfo']['store_name']}"
            missed.store k, info
        end
    end

    #existed.each {|e| puts "#{e} #{barcodeList[e]} "}
    puts "ordered: #{attrs.size} existed: #{existed.size} missed: #{leaked.size}"
    leaked.each { |k| puts "@bcList.store '#{k}', '' ##{missed[k]}" }
    leaked.each { |k| puts "@skus.store '#{k}', '' ##{missed[k]}" }

end

#2023-11-14为了解决barcodeList缺失数据的问题，重新完善eb_unify_pcode表，这是根据订单项目建立
def fixUnifyPcodeI

    uniks = []
    sql = "select id, product_attr_unique as unik from tutem_mall.eb_store_cart group by unik"
    (queryRds(sql)).each do |q|
        uniks += [q['unik']]
    end

    uniks.each do |unik|
        sql = "select cart_info from tutem_mall.eb_store_order_cart_info where cart_info like '%#{unik}%' limit 1"
        (@rds.query(sql)).each do |q|
            cart = JSON.parse q['cart_info']
            product = cart['productInfo']
            attr = cart['productInfo']['attrInfo']
            product_name = product['store_name']
            product_id = attr['product_id']
            pav_sku = attr['suk']
            pav_barcode = attr['bar_code']
            pav_barcode = "#{unik}-1" if pav_barcode.empty?
            erpcode = unik
            serve_days = 10
            comment = 'deactive'

            # Check if a record with the same pav_unique already exists in eb_unify_pcode
            xsql = "SELECT id FROM tutem_mall.eb_unify_pcode WHERE pav_unique = ?"
            xrecords = executeRds(xsql, unik)

            print xrecords.first.nil? ? '!' : '.'

            if xrecords.first.nil?
                # If no record exists, insert a new one
                isql = "INSERT INTO tutem_mall.eb_unify_pcode (product_id, product_name, pav_sku, pav_unique, pav_barcode, erpcode, serve_days, comment, add_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
                #executeRds(isql, product_id, product_name, pav_sku, unik, pav_barcode, erpcode, serve_days, comment, Time.now.to_i)
            end

        end
    end

end

#2023-11-14 为了解决barcodeList缺失数据的问题，重新完善eb_unify_pcode表，这是根据PAV表补足
def fixUnifyPcodeII

    uniks = []
    sql = "select v.id, v.unique as pav_unique, v.erp_code as erpcode, p.store_name as product_name, v.suk as pav_sku, v.product_id, v.bar_code as pav_barcode from tutem_mall.eb_store_product_attr_value v left join tutem_mall.eb_store_product p on v.product_id = p.id"
    (queryRds(sql)).each do |q|

        unik = q['pav_unique']
        product_id = q['product_id']
        product_name = q['product_name']
        pav_sku = q['pav_sku']
        pav_unique = q['pav_unique']
        pav_barcode = q['pav_barcode']
        pav_barcode = unik + '-1' if pav_barcode.empty?
        erpcode = q['erpcode']
        erpcode = unik if erpcode.empty?
        serve_days = 10
        comment = 'deactive'

        # Check if a record with the same pav_unique already exists in eb_unify_pcode
        xsql = "SELECT id FROM tutem_mall.eb_unify_pcode WHERE pav_unique = ?"
        xrecords = executeRds(xsql, unik)

        print xrecords.first.nil? ? '!' : '.'
        if xrecords.first.nil?
            # If no record exists, insert a new one
            isql = "INSERT INTO tutem_mall.eb_unify_pcode (product_id, product_name, pav_sku, pav_unique, pav_barcode, erpcode, serve_days, comment, add_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
            #executeRds(isql, product_id, product_name, pav_sku, pav_unique, pav_barcode, erpcode, serve_days, comment, Time.now.to_i)
        end
    end

end

#2023-11-15 在解决了barcodeList完备性问题之后针对所有订单中有MISSING标识的用户，重新生成订单标签
def fixOrder231115I
    sql = "select uid from tutem_mall.eb_user where goods_list like '%MISSING%'"
    recs = @rds.query(sql)
    recs.each do |rec|
        uid = rec['uid']
        print "#{uid} "
        #syncUserData uid
    end
end


#2024-01-17解决now_money余额不连续的问题
#在migrate-tutem.rb中有一个reset_now_money_bills也是大概的思路
def tupayBillUnbalanced? uid=1, showProgress=false, showDetail = false, breakUnbalanced=false, fixRecord = false

    foundUnbalanced = false
    puts "checkTupayBillBalance for UID##{uid}" if showProgress

    # 执行第一个 SQL 查询，获取用户账单记录
    result_user_bill = @rds.query("
        SELECT id, uid, pm, number, balance, title, mark, add_time
        FROM tutem_mall.eb_user_bill
        WHERE category = 'now_money' AND uid = #{uid}
        ORDER BY add_time asc
                                 ")

    initial_balance = 0
    expected_balance = 0

    # 遍历账单记录
    result_user_bill.each do |row|
        # 更新初始余额
        expected_balance += row['pm'] == 1 ? row['number'] : -row['number']

        # 检查余额是否一致
        row_balance = row['balance']
        if expected_balance != row_balance
            print "[#{row['id']}]" if showProgress
            gap = (expected_balance - row_balance).to_f.round(2)
            time = Time.at(row['add_time'])
            if showDetail
                puts "\n发现不一致余额记录：期初#{initial_balance.to_f.round(2)} 期待#{expected_balance.to_f.round(2)} 登记：#{row_balance.to_f.round(2)} 缺口：#{gap}"
                puts "ID: #{row['id']}, UID: #{row['uid']}, PM: #{row['pm']}, Number: #{row['number'].to_f.round(2)}, Balance: #{row_balance.to_f.round(2)}, Time: #{time}, Memo: #{row['title']} #{row['mark']}"
            end
            if fixRecord
                sql = "update tutem_mall.eb_user_bill set balance = #{expected_balance.to_f.round(2)} where id = #{row['id']}"
                begin
                    @rds.query(sql);
                rescue=>e
                    puts "ERROR found when executing sql: #{sql}"
                    exit
                end
            end
            foundUnbalanced = true
            return foundUnbalanced if breakUnbalanced
            initial_balance = expected_balance
        else
            print '.' if showProgress
            initial_balance = row_balance
        end

    end

    user_results = @rds.query("SELECT uid, real_name, now_money FROM tutem_mall.eb_user WHERE uid = #{uid}")
    # 提取用户信息
    user_info = user_results.first
    if user_info.nil?
        puts "uid not existed: #{uid}"
        return true
    end
    current_balance = user_info['now_money']

    if (current_balance != initial_balance)
        puts "bill balanced but current balance#{current_balance.to_f.round(2)} not matched billed #{expected_balance.to_f.round(2)}"
        foundUnbalanced = true
    end

    print "Done\n" if showProgress
    return foundUnbalanced

end

def fixTupayBills
    sql = "SELECT DISTINCT uid FROM tutem_mall.eb_user_bill WHERE category = 'now_money'"
    rows = @rds.query(sql)
    rows.each do |row|
        uid = row['uid']
        if tupayBillUnbalanced? uid, false, false, true
            puts "#UID #{uid} tupay bill unbalancy found"
            tupayBillUnbalanced? uid, true, true, false, true
            puts "请重新清空UID##{uid}的余额"
        end
    end
end

#2024-05-20 根据更新后的代表关系修复王梦阳的所有用户的订单标识
=begin
    select * from tutem_mall.eb_user_rep where user_id = 19232; 
    #1714924800
    ##2024-05-06 00:00:00
    ##2024-05-01 00:00:00
    #
    #update tutem_mall.eb_user_rep set until = UNIX_TIMESTAMP(STR_TO_DATE('2024-05-01 00:00:00', '%Y-%m-%d %H:%i:%s')) where id = 1329; #CST
    #update tutem_mall.eb_user_rep set since = UNIX_TIMESTAMP(STR_TO_DATE('2024-05-01 00:00:00', '%Y-%m-%d %H:%i:%s')) where id = 1758; #CST
    #
    ##找到所有王梦阳的用户，重置
=end
def fixOrderCreditByClient client_id
    sql = "select uid from tutem_mall.eb_user where spread_uid = #{client_id}"
    puts sql
    rows = @rds.query(sql)
    rows.each do |row|
        uid = row['uid']
        syncUserData uid
    end
end
#fixOrderCreditByClient 19232

def syncOrderTags cond = 'and pay_time >= 1719763200'
    update_all_pickup_goods_info cond
    sql = "select uid from tutem_mall.eb_store_order where paid = 1 #{cond}"
    (queryRds(sql)).each do |s|
        uid = s['uid']
        updateUserInfo uid
    end
end
#syncOrderTags 'and pay_time >= 1719763200'

#为eb_system_role编辑和整理权限：合并、去重
def mergeRoleMenus

    pics="362,276,363,362,364,362,386,362,389,362,406,399,459,462,472,462"
    picArray = pics.split(',');

    svcs = "23,24,146,147,261,262,263,264,265,266,25,142,143,369,370,488,490,26,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,394,177,398,399,400,401,402,403,405,406,407,150,283,38,39,40,41,72,501,492,480,485,486,487,224,225,226,227,495,237,0,285,151,148,80,474,289,493,489,21,499"
    admin = "23,24,146,147,264,265,266,263,261,262,25,142,143,369,370,285,26,190,191,394,208,207,206,198,199,200,201,202,203,204,205,197,196,192,193,194,195,329,334,335,290,170,224,225,226,227,228,151,177,398,399,407,406,400,401,402,403,405,176,449,408,409,410,411,412,413,456,337,353,371,450,415,286,148,149,229,230,231,232,233,234,235,150,352,271,254,366,368,367,272,333,238,273,473,241,349,351,350,287,306,239,307,179,217,219,218,216,312,308,313,288,293,341,340,296,318,317,316,315,314,294,300,301,302,244,295,305,304,303,374,246,297,319,321,320,269,376,451,422,423,424,425,426,427,11,360,17,127,128,126,174,452,416,421,420,417,418,419,355,356,357,358,359,354,12,97,98,99,30,93,89,124,92,91,90,123,31,94,95,96,37,19,73,42,115,116,43,44,45,82,133,134,395,396,397,84,236,129,132,289,1,470,468,112,111,53,54,55,56,117,119,108,48,144,145,118,361,270,462,471,472,466,458,459,460,461,463,464,465,372,373,153,8,63,104,105,64,65,66,67,4,68,59,102,103,62,61,60,2,74,100,75,76,77,101,78,247,248,249,250,474,478,475,476,477,479,378,255,80,283,72,38,41,39,40,284,71,33,113,114,34,35,36,175,209,210,222,223,211,212,213,214,215,21,469,130,131,173,276,362,365,364,363,386,387,388,389,390,392,393,453,278,377,447,439,444,445,446,443,442,441,440,379,448,380,382,381,467,9,52,109,110,57,58,70,7,46,106,107,47,49,50,51,69,252,237,408,481,0"
    opr = "23,24,146,147,261,262,263,264,265,266,25,142,143,369,370,488,490,285,26,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,394,329,334,335,151,177,398,399,400,401,402,403,405,406,407,456,496,337,353,371,415,450,286,148,149,229,230,231,232,233,234,235,150,271,254,366,367,368,272,238,333,273,241,473,349,350,351,287,306,239,307,179,216,217,218,219,312,308,313,288,293,340,341,296,314,315,316,317,318,294,244,300,301,302,295,246,303,304,305,374,297,319,320,321,269,376,422,423,424,425,426,427,451,11,360,17,126,127,128,174,416,417,418,419,420,421,452,354,12,97,98,99,30,89,90,91,92,123,124,93,31,37,94,95,96,19,42,43,44,45,115,116,73,82,133,134,84,129,132,236,395,396,397,80,499,283,38,39,40,41,72,284,33,34,35,36,113,114,71,500,501,462,372,373,458,459,460,461,463,464,465,466,471,472,481,482,484,491,175,209,210,211,212,213,214,215,222,223,492,255,237,0,289,474,21"

    #array = svcs.split(',');
    array = opr.split(',');
    

    newArray = picArray + array

    arr2 = []
    newArray.each do |a|
      arr2 += [a.to_i]
    end
    arr3 = arr2.uniq.sort!
    puts arr3.join(',')

end
#mergeRoleMenus


#通过订单备注，将退款记录补登到eb_refund表中
def migrate_missing_refund_data

    @tmall = Mysql2::Client.new({ host: ENV['RDS_AGENT'], username: 'psi_root', port: 1403, password: ENV['PSI_PASSWORD'], database: 'tutem_mall' }) if @tmall.nil?
    refund_pattern = /\[(\d{2}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\] #(\d+)微信退款([\d\.]+)\（(.*?)\）/um
    # 查询包含退款信息的订单
    sql = "SELECT o.id, o.uid, u.spread_uid as cid, o.rep_code, o.remark, o.merchant_id, o.order_id, o.pay_time, o.pay_price FROM tutem_mall.eb_store_order o left join tutem_mall.eb_user u on o.uid = u.uid WHERE o.remark LIKE '%微信退款%' AND o.paid = 1 AND o.pay_type = 'weixin';"
    orders = queryRds(sql)


    # 处理每个订单的退款信息
    orders.each do |order|
      order['remark'].scan(refund_pattern).each do |match|
        refund_timestamp_str, admin_id_str, refund_amount_str, refund_reason = match
        refund_time = DateTime.strptime(refund_timestamp_str, '%y-%m-%d %H:%M:%S').to_time.to_i
        refund_amount = refund_amount_str.to_f.round(2)
        admin_id = admin_id_str.to_i

        # 构建插入eb_refund表的SQL语句
        insert_sql = "INSERT INTO tutem_mall.eb_refund (
          order_id, uid, cid, rep_code, wx_merchant_id, wx_order_id, pay_time, pay_amount,
          admin_id, refund_time, refund_reason, refund_amount, balance
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0.00)"

        params = [
          order['id'], order['uid'], order['cid'], order['rep_code'], order['merchant_id'], order['order_id'], order['pay_time'], order['pay_price'],
          admin_id,
          refund_time,
          refund_reason.gsub(/"/, "'"), # 替换退款原因中的双引号为单引号以避免SQL错误
          refund_amount
        ]

        begin
            statement = @tmall.prepare(insert_sql)
            results = statement.execute(*params)
        rescue=>e
            puts e.message
            puts "skip migration params:"
            puts insert_sql
            ap params
            puts ""
            exit
        end

      end
    end

end
#migrate_missing_refund_data

def syncOrderTags cond = 'and pay_time >= 1719763200'
    update_all_pickup_goods_info cond
    sql = "select uid from tutem_mall.eb_store_order where paid = 1 #{cond}"
    (queryRds(sql)).each do |s|
        uid = s['uid']
        updateUserInfo uid
    end
end

#在更新3018的REP关系后重置订单
#syncOrderTags 'and uid in (select uid from tutem_mall.eb_user where spread_uid in (3018, 53, 9670, 15796, 20450, 20901) and paid = 1) '
